[Redshift] CHAR型のパターンマッチング

Redshift では LIKE 以外にも正規表現を用いたパターンマッチング条件を利用することができます。

• パターンマッチング条件 - Amazon Redshift

下記のようなテーブルから、「数字だけで構成されるレコードのみ抽出」したい場合は

value [VARCHAR]
1234567890
2345678901
ABCDEFGHIJ
KLMNOPQRST
NULL

条件式を

value ~ '^[[:digit:]]+$'

などとします。

SELECT * FROM (
  SELECT '1234567890'::varchar AS value UNION
  SELECT '2345678901'::varchar AS value UNION
  SELECT 'ABCDEFGHIJ'::varchar AS value UNION
  SELECT 'KLMNOPQRST'::varchar AS value UNION
  SELECT NULL::varchar AS value
) WHERE value ~ '^[[:digit:]]+$';

value     
----------
1234567890
2345678901

しかし、データ型が CHAR 型の場合には注意が必要です。

value [CHAR(20)]
1234567890
2345678901
ABCDEFGHIJ
KLMNOPQRST
NULL

等価条件（=）の場合、以下のようなクエリでレコードが取得できるため

SELECT * FROM (
  SELECT '1234567890'::varchar AS value UNION
  SELECT '2345678901'::varchar AS value UNION
  SELECT 'ABCDEFGHIJ'::varchar AS value UNION
  SELECT 'KLMNOPQRST'::varchar AS value UNION
  SELECT NULL::varchar AS value
) WHERE value = '1234567890';

value     
----------
1234567890

ついつい先程と同様に「'^[[:digit:]]+$'」でマッチングできそうに思ってしまいますが

SELECT * FROM (
  SELECT '1234567890'::char(20) AS value UNION
  SELECT '2345678901'::char(20) AS value UNION
  SELECT 'ABCDEFGHIJ'::char(20) AS value UNION
  SELECT 'KLMNOPQRST'::char(20) AS value UNION
  SELECT NULL::char(20) AS value
) WHERE value ~ '^[[:digit:]]+$';

value     
----------

マッチングしません。

「文字型 - Amazon Redshift」を参照すると以下のようにあります。

固定長の文字列を格納するには、CHAR または CHARACTER を使用します。これらの文字列は空白で埋められるので、CHAR(10) 列は常に 10 バイトのストレージを占有します。

CHAR 型データをパターンマッチングする場合はこの「空白で埋められる」を考慮する必要があります。

SELECT * FROM (
  SELECT '1234567890'::char(20) AS value UNION
  SELECT '2345678901'::char(20) AS value UNION
  SELECT 'ABCDEFGHIJ'::char(20) AS value UNION
  SELECT 'KLMNOPQRST'::char(20) AS value UNION
  SELECT NULL::char(20) AS value
) WHERE value ~ '^[[:digit:]]+[[:space:]]*$';

value               
--------------------
1234567890          
2345678901          

マッチングさせることができました。

まとめ

値としては同じなのですが、データ型によって正規表現のパターンが異なることに注意が必要です。 また、ドキュメントに「“SIMILAR TO または POSIX の演算子を使用する正規表現マッチングは、計算コストが高くなります。”」とあるように、多くの行を処理する場合のパターンマッチングなどにも注意が必要です。